package com.skeeey.aim.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
//import java.text.ParseException;
//import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.skeeey.aim.model.AutoRecord;
import com.skeeey.aim.model.statistics.StatisticRecord;

public class CreateStatisticExcel {

	private static File templetFile = new File(AIMUtil.getSystemConfig("execl.templet.path"));

	// cover
	private static Workbook create(StatisticRecord record, Date createDate) {
		InputStream in = null;
		Workbook workbook = null;
		try {
			in = new FileInputStream(templetFile);
			workbook = new HSSFWorkbook(in);
			long netCode = Long.parseLong(record.getDealer().getNetCode());
			String dealerName = record.getDealer().getName();
			// 封面
			Sheet cover = workbook.getSheetAt(0);
			// E6
			cover.getRow(5).getCell(4).setCellValue(netCode);
			// D9
			//cover.getRow(8).getCell(3).setCellValue(record.getDealer().getName());
			// E12
			//cover.getRow(11).getCell(4).setCellValue(record.getDealer().getShortName());
			// E15
			//cover.getRow(14).getCell(4).setCellValue(record.getDealer().getProvince());
			// E18
			//cover.getRow(17).getCell(4).setCellValue(record.getDealer().getCity());
			// E22
			//cover.getRow(21).getCell(4).setCellValue(record.getDealer().getRssc());
			// E25
			cover.getRow(24).getCell(4).setCellValue(createDate);
			// E28
			cover.getRow(27).getCell(4).setCellValue(createDate);
			// E31
			cover.getRow(30).getCell(4).setCellValue(record.getUserName());
			// 状态一
			Sheet status1 = workbook.getSheetAt(2);
			int ri = 5;
			List<AutoRecord> records_0 = record.getRecordsBy(0);
			for (AutoRecord r : records_0) {
				Row row =  status1.getRow(ri);
				//B6
				//row.getCell(1).setCellValue(netCode);
				//row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(Long.parseLong(r.getOrderStartDate()));
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getAutoName());
				row.getCell(8).setCellValue(r.getColorNumber());
				row.getCell(9).setCellValue(r.getColorName());
				row.getCell(10).setCellValue(r.getStorageLocation());
				//L6
				//row.getCell(11).setCellValue(r.getAutoKind());
//				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
//				try {
//					row.getCell(12).setCellValue(simpleDateFormat.parse(r.getOrderStartDate()));
//				} catch (ParseException e) {
//					//do nothing
//				}
				//row.getCell(13).setCellValue(createDate);
				//row.getCell(14).setCellValue(r.getActualAge());
				ri++;
			}
			// 状态二
			Sheet status2 = workbook.getSheetAt(3);
			ri = 6;
			List<AutoRecord> records_1 = record.getRecordsBy(1);
			for (AutoRecord r : records_1) {
				Row row =  status2.getRow(ri);
				//B7
//				row.getCell(1).setCellValue(netCode);
//				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(r.getOrderStartDate());
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getAutoName());
				row.getCell(8).setCellValue(r.getColorNumber());
				row.getCell(9).setCellValue(r.getColorName());
//				row.getCell(10).setCellValue(r.getAutoKind());
				ri++;
			}
			// 状态三
			Sheet status3 = workbook.getSheetAt(4);
			ri = 6;
			List<AutoRecord> records_2 = record.getRecordsBy(2);
			for (AutoRecord r : records_2) {
				Row row =  status3.getRow(ri);
				//B7
//				row.getCell(1).setCellValue(netCode);
//				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(r.getRetailTime());
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getColorNumber());
				row.getCell(8).setCellValue(r.getColorName());
				row.getCell(9).setCellValue(r.getStorageLocation());
				// set a default value
				row.getCell(10).setCellValue("虚拟出库");
				row.getCell(11).setCellValue("√");
				// start with P(15)
//				row.getCell(15).setCellValue(r.getAutoName());
//				row.getCell(16).setCellValue(r.getColorNumber());
//				row.getCell(17).setCellValue(r.getColorName());
//				row.getCell(18).setCellValue(r.getAutoKind());
//				row.getCell(19).setCellValue(r.getOrderStartDate());
//				row.getCell(20).setCellValue(createDate);
//				row.getCell(21).setCellValue(r.getActualAge());
				ri++;
			}
			// 系统外
			Sheet outSystem = workbook.getSheetAt(5);
			ri = 6;
			List<AutoRecord> records_3 = record.getRecordsBy(3);
			for (AutoRecord r : records_3) {
				Row row =  outSystem.getRow(ri);
				row.getCell(1).setCellValue(netCode);
				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(r.getOrderStartDate());
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getColorNumber());
				row.getCell(8).setCellValue(r.getColorName());
				row.getCell(9).setCellValue(r.getStorageLocation());
				row.getCell(11).setCellValue("√");
				row.getCell(13).setCellValue(r.getAutoName());
				row.getCell(14).setCellValue(r.getColorNumber());
				row.getCell(15).setCellValue(r.getColorName());
				row.getCell(16).setCellValue(r.getAutoKind());
				row.getCell(17).setCellValue(r.getOrderStartDate());
				row.getCell(18).setCellValue(createDate);
				row.getCell(19).setCellValue(r.getActualAge());
				ri++;
			}
			// 未提
			Sheet unPick = workbook.getSheetAt(6);
			ri = 3;
			List<AutoRecord> records_4 = record.getRecordsBy(5);
			for (AutoRecord r : records_4) {
				Row row =  unPick.getRow(ri);
				//B4
//				row.getCell(1).setCellValue(netCode);
//				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(Long.parseLong(r.getOrderStartDate()));
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getAutoName());
				row.getCell(8).setCellValue(r.getColorNumber());
				row.getCell(9).setCellValue(r.getColorName());
//				row.getCell(10).setCellValue(r.getAutoKind());
				ri++;
			}
			// 在途
			Sheet way = workbook.getSheetAt(7);
			ri = 3;
			List<AutoRecord> records_5 = record.getRecordsBy(4);
			for (AutoRecord r : records_5) {
				Row row =  way.getRow(ri);
//				row.getCell(1).setCellValue(netCode);
//				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(Long.parseLong(r.getOrderStartDate()));
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getAutoName());
				row.getCell(8).setCellValue(r.getColorNumber());
				row.getCell(9).setCellValue(r.getColorName());
//				row.getCell(10).setCellValue(r.getAutoKind());
				ri++;
			}
			// 虚拟未查到
			Sheet virtual = workbook.getSheetAt(8);
			ri = 3;
			List<AutoRecord> records_6 = record.getRecordsBy(6);
			for (AutoRecord r : records_6) {
				Row row =  virtual.getRow(ri);
//				row.getCell(1).setCellValue(netCode);
//				row.getCell(2).setCellValue(dealerName);
				row.getCell(3).setCellValue(r.getRetailTime());
				row.getCell(4).setCellValue(r.getVINCode());
				try {
					row.getCell(5).setCellValue(Long.parseLong(r.getEnginCode()));
				} catch (NumberFormatException e) {
					row.getCell(5).setCellValue(r.getEnginCode());
				}
				row.getCell(6).setCellValue(r.getAutoCode());
				row.getCell(7).setCellValue(r.getColorName());
//				row.getCell(12).setCellValue(r.getAutoName());
//				row.getCell(13).setCellValue(r.getColorNumber());
//				row.getCell(14).setCellValue(r.getColorName());
//				row.getCell(15).setCellValue(r.getAutoKind());
//				row.getCell(16).setCellValue(r.getOrderStartDate());
//				row.getCell(17).setCellValue(createDate);
//				row.getCell(18).setCellValue(r.getAutoAge());
				ri++;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		return workbook;
	}

	private static void updateFormula(Workbook workbook) {
		FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
		evaluator.evaluateAll();
	}

	public static Date createStatisticExcel(StatisticRecord record, String fileName, String ts) throws IOException {
		Date createDate = new Date();
		Workbook workbook = create(record, createDate);
		updateFormula(workbook);
		File sourceFile = new File(fileName);
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(sourceFile);
			workbook.write(out);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		String destName = record.getDealer().getNetCode() + "-" + ts;
		FileUtils.copyFile(sourceFile, new File(AIMUtil.getSystemConfig("execl.temp.path") + File.separator + destName + ".xls"));
		return createDate;
	}
	
	public static void main(String[] args) {
		String str = "E1234";
		Long.parseLong(str);
	}
}
